from openpyxl import load_workbook

filename = r'../a_datas/result_openpyxl.xlsx'
# 1.workbook
workbook = load_workbook(filename)
# workbook info
names = workbook.sheetnames  # workbook.get_sheet_names()已弃用  # 所有sheet name 格式list

# 2.sheet
sheet = workbook['2021年']  # sheet = workbook.get_sheet_by_name('2021年') # 已经弃用
sheet_active = workbook.active  # 当前sheet
# sheet info
title = sheet.title  # '2021年'
max_row = sheet.max_row  # 已有数据最大行数
max_column = sheet.max_column  # 已有数据最大列数

# 3.cell
cell_B2 = sheet['B2']  # B为列 2为行
cell_42 = sheet.cell(row=4, column=2)

# 单元格信息
row = cell_42.row  # 行标
column = cell_42.column  # 列标
value = cell_42.value  # 值

# 4.rows 和 columns
rows = sheet.rows  # 返回tuple()包裹的row
for row in rows:
    for cell in row:
        cell_value = cell.value

columns = sheet.columns
for c in columns:
    for cell in c:
        cell_value = cell.value

# 通过转换为list获取某行
rows_list = list(sheet.rows)
row2 = rows_list[1]

# 5.区域
for i in range(1, 4):
    for j in range(1, 5):
        cell = sheet.cell(row=i, column=j)

area = sheet['A1':'B3']  # A1左上角 B3 右下角 返回tuple

# 根据字母获得列号，根据列号返回字母
from openpyxl.utils import get_column_letter, column_index_from_string

letter = get_column_letter(2)  # 2为B
num = column_index_from_string('B')  # B为2


